<?php

/**
 * @version		$Id: hello.php 15 2009-11-02 18:37:15Z chdemko $
 * @package		Joomla16.Tutorials
 * @subpackage	Components
 * @copyright	Copyright (C) 2005 - 2010 Open Source Matters, Inc. All rights reserved.
 * @author		Christophe Demko
 * @link		http://joomlacode.org/gf/project/helloworld_1_6/
 * @license		License GNU General Public License version 2 or later
 */

// No direct access to this file
defined('_JEXEC') or die('Restricted access');

// import Joomla modelitem library
jimport('joomla.application.component.modelitem');

/**
 * HelloWorld Model
 */
class folderModelFolder extends JModel
{
    //get folder list to display tin dropdown
    public function getFolderList($user_name){
        $sql = "SELECT * FROM fome WHERE FOME_USEM_CODE = '$user_name' AND FOME_PARENT = 0";
        $this->_db->setQuery($sql);
        return $this->_db->loadObjectList();
    }
    
    
    //create a new folder
    public function createFolder($name, $parent, $user_name){
        $code = HelperBG::getIDAuto('fome', 'FOME_CODE');
        $sql = "INSERT INTO fome (FOME_CODE, FOME_NAME, FOME_PARENT, FOME_USEM_CODE) values ($code, '$name', $parent, '$user_name')";
        $this->_db->setQuery($sql);
        if($this->_db->query()){
            return array('status' => 'ok');
        } 
        return array('status' => 'fail');
    }
    
    //delete folder
    public function deleteFolder($user_name, $code){
        try{
            //delete subfolder
            
            $this->_db->setQuery("SELECT * FROM fome WHERE FOME_PARENT = $code AND FOME_USEM_CODE = '$user_name'");
            $sub_folder = $this->_db->loadObjectList();
            if(!empty($sub_folder)){
                foreach($sub_folder as $sub){
                    $this->deleteFolder($user_name, $sub->FOME_CODE);
                }
            }
            
            //delete folder
            $this->_db->setQuery("DELETE FROM fome WHERE FOME_CODE = $code AND FOME_USEM_CODE = '$user_name'");
            if($this->_db->query()){
                //delete sent messages
                $this->_db->setQuery("DELETE FROM mese WHERE MESE_FOME_CODE = $code");
                $this->_db->query();
                
                //delete recipment messages
                $this->_db->setQuery("DELETE FROM mere WHERE MERE_FOME_CODE = $code");
                $this->_db->query();
            }
            
            return array('status' => 'ok');
            
        } catch(Exception $e){
            return array('status' => 'fail');
        }
    }
    public function getAllFolders($idUser){
        
    	$query = "SELECT * FROM fome WHERE FOME_USEM_CODE = $idUser";
        $this->_db->setQuery($query);
       
        return $this->_db->loadObjectList();
    }
    //get folders for display in table
    public function getFolders($user_name){
        $sql = "SELECT * FROM fome WHERE FOME_USEM_CODE = '$user_name'";
        $this->_db->setQuery($sql);
        $folders = $this->_db->loadObjectList();
        
        $result = array();
        
        foreach($folders as $folder){
            //count read messages
            $this->_db->setQuery("SELECT COUNT(*) as _read FROM mere WHERE MERE_FOME_CODE = $folder->FOME_CODE AND MERE_CHKF = 1");
            $read = $this->_db->loadObject()->_read;
            
            $this->_db->setQuery("SELECT COUNT(*) as _read FROM mese WHERE MESE_FOME_CODE = $folder->FOME_CODE AND MESE_CHKF = 1");
            $read += $this->_db->loadObject()->_read;
            
            $this->_db->setQuery("SELECT COUNT(*) as unread FROM mere WHERE MERE_FOME_CODE = $folder->FOME_CODE AND (MERE_CHKF = 0 OR ISNULL(MERE_CHKF))");
            $unread = $this->_db->loadObject()->unread;
            
            array_push($result, (object) array(
                'name' => $folder->FOME_NAME,
                'code' => $folder->FOME_CODE,
                'read' => empty($read) ? 0 : $read,
                'unread' => empty($unread) ? 0 : $unread,
                'parent' => $folder->FOME_PARENT,
            ));
        }
        
        return (object) $result;
    }
    
    //edit folder
    public function editFolder($name, $parent, $code, $user_name){
        $sql = "UPDATE fome SET FOME_NAME='$name', FOME_PARENT=$parent WHERE FOME_CODE = $code AND FOME_USEM_CODE = '$user_name'";
        $this->_db->setQuery($sql);
        if($this->_db->query()){
            return array('status' => 'ok', 'sql' =>$sql);
        } 
        return array('status' => 'fail');
    }
    
    //clear folder
    public function clearFolder($user_name, $code){
        try{
            switch($code){
                case 'inbox':
                    $this->_db->setQuery("UPDATE mere SET MERE_DELF=1 
                    WHERE MERE_OCBA_CODE = '$user_name'                         
                        AND (MERE_FOME_CODE = 0 OR ISNULL(MERE_FOME_CODE)) ");
                    $this->_db->query();
                break;
                
                case 'sent':
                    $this->_db->setQuery("DELETE FROM mese                        
                        WHERE (MESE_DRAF = 0 OR ISNULL(MESE_DRAF))
                            AND  MESE_USEM_CODE = '$user_name'
                            AND (MESE_FOME_CODE = 0 OR ISNULL(MESE_FOME_CODE)) ");
                    $this->_db->query();
    
                break;
                
                case 'trash':
                    $this->_db->setQuery("DELETE FROM mere WHERE MERE_DELF=1 AND MERE_OCBA_CODE = '$user_name'");
                    $this->_db->query();
                    
                break;
                
                case 'draft':
                    $this->_db->setQuery("DELETE FROM mese                        
                        WHERE MESE_DRAF = 1
                            AND  MESE_USEM_CODE = '$user_name'
                            AND (MESE_FOME_CODE = 0 OR ISNULL(MESE_FOME_CODE)) ");
                    $this->_db->query();
                    
                break;
                
                default:
                    //delete subfolder            
                    //$this->_db->setQuery("SELECT * FROM fome WHERE FOME_PARENT = $code AND FOME_USEM_CODE = '$user_name'");
//                    $sub_folder = $this->_db->loadObjectList();
//                    if(!empty($sub_folder)){
//                        foreach($sub_folder as $sub){
//                            $this->deleteFolder($user_name, $sub->FOME_CODE);
//                        }
//                    }
                    
                    //delete sent messages
                    $this->_db->setQuery("DELETE FROM mese WHERE MESE_FOME_CODE = $code AND MESE_USEM_CODE = '$user_name'");
                    $this->_db->query();
                    
                    //delete recipment messages
                    $this->_db->setQuery("DELETE FROM mere WHERE MERE_FOME_CODE = $code AND MERE_OCBA_CODE = '$user_name'");
                    $this->_db->query();
            }
            
            return array('status' => 'ok');
            
        } catch(Exception $e){
            return array('status' => 'fail');
        }
    }
    
    //get inbox, sent, trash, draft
    public function getMainFolders($user_name){
        //$user_name = "'2001'";
        $data = array();
        
        //get inbox
        $inbox = array();
        $sql_inbox_read = "SELECT COUNT(*) as _read FROM mere 
        WHERE MERE_CHKF = 1 
            AND MERE_OCBA_CODE = '$user_name' 
            AND (MERE_DELF = 0 OR ISNULL(MERE_DELF)) 
            AND (MERE_FOME_CODE = 0 OR ISNULL(MERE_FOME_CODE))";
        
       
        $this->_db->setQuery($sql_inbox_read);
        $inbox['read'] = (int)$this->_db->loadObject()->_read;
        
        $sql_inbox_unread = "SELECT COUNT(*) as unread FROM mere 
        WHERE (MERE_CHKF = 0 OR ISNULL(MERE_CHKF)) 
            AND MERE_OCBA_CODE = '$user_name' 
            AND (MERE_DELF = 0 OR ISNULL(MERE_DELF))
            AND (MERE_FOME_CODE = 0 OR ISNULL(MERE_FOME_CODE))";
         
        $this->_db->setQuery($sql_inbox_unread);
        $inbox['unread'] = (int)$this->_db->loadObject()->unread;
        
        //push inbox
        $data['inbox'] = (object)$inbox;
        //array_push($data, (object)$inbox);
        
        //get sent
        $sent = array();
        $sql_sent = "SELECT COUNT(*) as _read FROM mese 
            WHERE MESE_USEM_CODE = '$user_name' 
            AND (MESE_DELF = 0 OR ISNULL(MESE_DELF)) 
            AND (MESE_DRAF = 0 OR ISNULL(MESE_DRAF))
            AND (MESE_FOME_CODE = 0 OR ISNULL(MESE_FOME_CODE))";
        $this->_db->setQuery($sql_sent);
        $sent['read'] = (int)$this->_db->loadObject()->_read;
        
        //push sent
        $data['sent'] = (object)$sent;
        //array_push($data, (object)$sent);
        
        //get trash
        $trash = array();
        $sql_trash_inbox_unread = "SELECT COUNT(*) as unread FROM mere 
            WHERE (MERE_CHKF = 0 OR ISNULL(MERE_CHKF)) 
            AND MERE_OCBA_CODE = '$user_name' 
            AND MERE_DELF = 1
            AND (MERE_FOME_CODE = 0 OR ISNULL(MERE_FOME_CODE))";
        $this->_db->setQuery($sql_trash_inbox_unread);
        $trash['unread'] = (int)$this->_db->loadObject()->unread;
        
//        $sql_deleted_sent = "SELECT COUNT(*) as _read FROM mese WHERE MESE_USEM_CODE = '$user_name' AND MESE_DELF = 1 AND (MESE_DRAF = 0 OR ISNULL(MESE_DRAF))";
//        $this->_db->setQuery($sql_deleted_sent);
//        $deleted_sent = (int)$this->_db->loadObject()->_read;
        
//        $sql_deleted_sent_draft = "SELECT COUNT(*) as _read FROM mese WHERE MESE_USEM_CODE = '$user_name' AND MESE_DELF = 1 AND MESE_DRAF = 1";
//        $this->_db->setQuery($sql_deleted_sent_draft);
//        $deleted_sent_draft = (int)$this->_db->loadObject()->_read;
        
        $sql_deleted_inbox_read = "SELECT COUNT(*) as _read FROM mere 
        WHERE MERE_CHKF = 1 
            AND MERE_OCBA_CODE = '$user_name' 
            AND MERE_DELF = 1
            AND (MERE_FOME_CODE = 0 OR ISNULL(MERE_FOME_CODE))";
        $this->_db->setQuery($sql_deleted_inbox_read);
        $deleted_inbox_read = (int)$this->_db->loadObject()->_read;
        
        //$trash['read'] = $deleted_sent + $deleted_sent_draft + $deleted_inbox_read;
        $trash['read'] = $deleted_inbox_read;
        
        //push trash
        $data['trash'] = (object)$trash;
        //array_push($data, (object)$trash);
        
        //get draft
        $draft = array();
        $sql_draft = "SELECT COUNT(*) as _read FROM mese 
        WHERE MESE_USEM_CODE = '$user_name' 
            AND (MESE_DELF = 0 OR ISNULL(MESE_DELF) )
            AND MESE_DRAF = 1
            AND (MESE_FOME_CODE = 0 OR ISNULL(MESE_FOME_CODE))";
        $this->_db->setQuery($sql_draft);
        $draft['read'] = (int)$this->_db->loadObject()->_read;
        
        //push draft
        $data['draft'] = (object)$draft;
        //array_push($data, (object)$draft);
        
        return (object)$data;
        
    }
}
